# ========================================
# RedFire PostgreSQL分析数据库 Dockerfile
# 版本: v5.0.0 (2025-09-16)
# 基于: TimescaleDB (PostgreSQL 15)
# 功能: 分析数据库和机器学习
# ========================================

FROM timescale/timescaledb:latest-pg15

# 维护者信息
LABEL maintainer="RedFire Team <team@redfire.com>"
LABEL version="5.0.0"
LABEL description="RedFire PostgreSQL Analytics Database with TimescaleDB"

# 设置环境变量
ENV POSTGRES_DB=redfire_analytics
ENV POSTGRES_USER=redfire_analytics
ENV POSTGRES_PASSWORD=redfire_pg_2025
ENV TZ=Asia/Shanghai
ENV PGTZ=Asia/Shanghai

# 切换到root用户进行系统配置
USER root

# 安装必要的工具和扩展 (Alpine Linux使用apk)
RUN apk update && apk add --no-cache \
    # 网络和系统工具
    curl \
    wget \
    # Python支持 (用于数据处理)
    python3 \
    py3-pip \
    # 基础Python包 (通过apk安装以避免编译)
    py3-psycopg2 \
    py3-redis \
    py3-sqlalchemy

# 设置时区
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

# 创建必要的目录 (以root用户身份)
RUN mkdir -p /var/log/postgresql \
    && mkdir -p /var/lib/postgresql/backups \
    && mkdir -p /docker-entrypoint-initdb.d/scripts \
    && chown -R postgres:postgres /var/log/postgresql \
    && chown -R postgres:postgres /var/lib/postgresql/backups

# 切换回postgres用户
USER postgres

# 复制自定义配置文件
COPY conf/postgresql.conf /etc/postgresql/postgresql.conf
COPY conf/pg_hba.conf /etc/postgresql/pg_hba.conf

# 复制初始化脚本
COPY scripts/ /docker-entrypoint-initdb.d/scripts/

# 设置脚本权限
USER root
RUN chmod +x /docker-entrypoint-initdb.d/scripts/*.sh
USER postgres

# 创建健康检查脚本
USER root
RUN echo '#!/bin/bash\n\
pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB} -h localhost\n\
if [ $? -eq 0 ]; then\n\
    echo "PostgreSQL is healthy"\n\
    exit 0\n\
else\n\
    echo "PostgreSQL is not healthy"\n\
    exit 1\n\
fi' > /usr/local/bin/health-check.sh \
    && chmod +x /usr/local/bin/health-check.sh

# 创建备份脚本
RUN echo '#!/bin/bash\n\
BACKUP_DIR="/var/lib/postgresql/backups"\n\
mkdir -p $BACKUP_DIR\n\
DATE=$(date +%Y%m%d_%H%M%S)\n\
pg_dump -U ${POSTGRES_USER} -h localhost ${POSTGRES_DB} > $BACKUP_DIR/postgres_backup_$DATE.sql\n\
if [ $? -eq 0 ]; then\n\
    echo "Backup completed: $BACKUP_DIR/postgres_backup_$DATE.sql"\n\
    # 压缩备份文件\n\
    gzip $BACKUP_DIR/postgres_backup_$DATE.sql\n\
    # 保留最近7天的备份\n\
    find $BACKUP_DIR -name "postgres_backup_*.sql.gz" -mtime +7 -delete\n\
else\n\
    echo "Backup failed"\n\
    exit 1\n\
fi' > /usr/local/bin/backup.sh \
    && chmod +x /usr/local/bin/backup.sh

# 创建数据清理脚本
RUN echo '#!/bin/bash\n\
psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "\n\
-- 清理过期的时序数据\n\
SELECT drop_chunks(interval '"'"'30 days'"'"', '"'"'vnpy_tick_history'"'"');\n\
SELECT drop_chunks(interval '"'"'90 days'"'"', '"'"'ml_features'"'"');\n\
SELECT drop_chunks(interval '"'"'180 days'"'"', '"'"'performance_analytics'"'"');\n\
-- 清理临时表\n\
DELETE FROM pg_stat_statements WHERE calls < 5 AND mean_time < 1;\n\
-- 执行VACUUM\n\
VACUUM ANALYZE;\n\
"\n\
if [ $? -eq 0 ]; then\n\
    echo "Data cleanup completed"\n\
else\n\
    echo "Data cleanup failed"\n\
    exit 1\n\
fi' > /usr/local/bin/cleanup.sh \
    && chmod +x /usr/local/bin/cleanup.sh

# 创建性能优化脚本
RUN echo '#!/bin/bash\n\
psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "\n\
-- 更新表统计信息\n\
ANALYZE;\n\
-- 重建索引 (如果需要)\n\
REINDEX DATABASE ${POSTGRES_DB};\n\
-- 压缩时序数据\n\
SELECT compress_chunk(chunk) FROM show_chunks('"'"'vnpy_tick_history'"'"') AS chunk;\n\
SELECT compress_chunk(chunk) FROM show_chunks('"'"'vnpy_bar_history'"'"') AS chunk;\n\
"\n\
if [ $? -eq 0 ]; then\n\
    echo "Performance optimization completed"\n\
else\n\
    echo "Performance optimization failed"\n\
    exit 1\n\
fi' > /usr/local/bin/optimize.sh \
    && chmod +x /usr/local/bin/optimize.sh

# 创建监控脚本
RUN echo '#!/bin/bash\n\
echo "=== PostgreSQL Status ==="\n\
psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "\n\
SELECT \n\
    version() as version,\n\
    current_database() as database,\n\
    current_user as user,\n\
    inet_server_addr() as server_addr,\n\
    inet_server_port() as server_port;\n\
"\n\
echo "=== Connection Stats ==="\n\
psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "\n\
SELECT \n\
    count(*) as total_connections,\n\
    count(*) FILTER (WHERE state = '"'"'active'"'"') as active_connections,\n\
    count(*) FILTER (WHERE state = '"'"'idle'"'"') as idle_connections\n\
FROM pg_stat_activity;\n\
"\n\
echo "=== Database Stats ==="\n\
psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "\n\
SELECT \n\
    schemaname,\n\
    tablename,\n\
    n_tup_ins as inserts,\n\
    n_tup_upd as updates,\n\
    n_tup_del as deletes,\n\
    n_live_tup as live_tuples,\n\
    n_dead_tup as dead_tuples\n\
FROM pg_stat_user_tables \n\
ORDER BY n_live_tup DESC \n\
LIMIT 10;\n\
"\n\
echo "=== TimescaleDB Hypertables ==="\n\
psql -U ${POSTGRES_USER} -d ${POSTGRES_DB} -c "\n\
SELECT \n\
    hypertable_name,\n\
    num_chunks,\n\
    compression_enabled,\n\
    compressed_chunks\n\
FROM timescaledb_information.hypertables;\n\
"' > /usr/local/bin/monitor.sh \
    && chmod +x /usr/local/bin/monitor.sh

# 切换回postgres用户
USER postgres

# 暴露端口
EXPOSE 5432

# 健康检查
HEALTHCHECK --interval=30s --timeout=10s --start-period=60s --retries=3 \
    CMD /usr/local/bin/health-check.sh

# 设置工作目录
WORKDIR /var/lib/postgresql/data

# 使用官方入口点
ENTRYPOINT ["docker-entrypoint.sh"]

# 启动命令
CMD ["postgres", \
    "-c", "config_file=/etc/postgresql/postgresql.conf", \
    "-c", "hba_file=/etc/postgresql/pg_hba.conf", \
    "-c", "log_directory=/var/log/postgresql", \
    "-c", "log_filename=postgresql-%Y-%m-%d_%H%M%S.log", \
    "-c", "logging_collector=on", \
    "-c", "log_rotation_age=1d", \
    "-c", "log_rotation_size=100MB", \
    "-c", "log_min_duration_statement=1000", \
    "-c", "log_line_prefix=%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ", \
    "-c", "log_checkpoints=on", \
    "-c", "log_connections=on", \
    "-c", "log_disconnections=on", \
    "-c", "log_lock_waits=on", \
    "-c", "log_temp_files=0", \
    "-c", "log_autovacuum_min_duration=0"]
